/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.execute;

import db.ConnectionProperties;
import db.definitions.ColumnDefinition;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import org.postgresql.*;

/**
 *
 * @author abracadabuda
 */
public class Connect {

    private Connection con = null;
    private DatabaseMetaData dbmd;

    /**
     * connects to database by host, user, password, database
     * @param host host:port
     * @param user user
     * @param heslo password
     * @param databaza database
     * @throws java.sql.SQLException if connection could not be created
     * @throws java.lang.ClassNotFoundException if driver was not found
     */
    public Connect(String host, String user, String heslo, String databaza) throws SQLException, ClassNotFoundException {
        con = connectByDriver(host, user, heslo, databaza);
        dbmd = con.getMetaData();
    }

    /**
     * connect to database by connection properties
     * @param conProp properties for connection
     * @throws java.sql.SQLException if connection could not be created
     * @throws java.lang.ClassNotFoundException if driver was not found
     */
    public Connect(ConnectionProperties conProp) throws SQLException, ClassNotFoundException {
        con = connectByDriver(conProp.getHost(), conProp.getUsername(), conProp.getPassword(), conProp.getDatabaseName());
        dbmd = con.getMetaData();
    }

    /**
     * real connect to DB by driver
     * @param host host:port
     * @param user user
     * @param heslo password
     * @param databaza database
     * @return connection to datbase
     * @throws java.sql.SQLException if connection could not be created
     * @throws java.lang.ClassNotFoundException if driver was not found
     */
    private Connection connectByDriver(String host, String user, String heslo, String databaza) throws SQLException, ClassNotFoundException {
        Class.forName("org.postgresql.Driver");
        Properties prop = new Properties();
        prop.setProperty("user", user);
        prop.setProperty("password", heslo);
        String url = new String("jdbc:postgresql://" + host + "/" + databaza);
        return DriverManager.getConnection(url, user, heslo);
    //return DriverManager.getConnection(url, prop);
    }

    /**
     * displays tables for schema
     * @param schema name of schema 
     * @return tables in schema
     * @throws java.sql.SQLException if database error occurred
     */
    public ArrayList<String> displayTables(String schema) throws SQLException {
        String[] a = {"TABLE", "VIEW"};
        ResultSet rs = dbmd.getTables(null, schema, null, a);
        ArrayList<String> tables = new ArrayList<String>();
        while (rs.next()) {
            tables.add(rs.getString("table_name"));
        }
        return tables;
    }

    /**
     * displays schema of current selected database
     * @return schemas in database
     * @throws java.sql.SQLException if database error occurred
     */
    public ArrayList<String> displaySchemas() throws SQLException {
        ResultSet rs = dbmd.getSchemas();
        ArrayList<String> schemas = new ArrayList<String>();
        while (rs.next()) {
            if (validSchemaName(rs.getString("table_schem"))) {
                schemas.add(rs.getString("table_schem"));
            }
        }
        return schemas;
    }

    private boolean validSchemaName(String schema) {
        return schema.equals("information_schema") || schema.startsWith("pg_") || schema.equals("") ? false : true;
    }

    /**
     * gets primary keys - as array of columnDefinition (name and type of column) of table in catalog & schema
     * @param catalog catalog for table, could be null
     * @param schema schema for table, could be null
     * @param table table from which primary keys are selected
     * @return list of ColumnDefinition
     * @throws java.sql.SQLException if database error occurred
     */
    public ArrayList<ColumnDefinition> getPK(String catalog, String schema, String table) throws SQLException {
        ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, table);
        ArrayList<ColumnDefinition> pks = new ArrayList<ColumnDefinition>();
        while (rs.next()) {
            ColumnDefinition colPK = new ColumnDefinition();
            colPK.setColName(rs.getString("column_name"));
            ResultSet rsColumn = dbmd.getColumns(catalog, schema, table, rs.getString("column_name"));
            while (rsColumn.next()) {
                colPK.setType(rsColumn.getString("type_name"));
            }
            pks.add(colPK);
        }
        //if none primary keys in tables, select all columns from table
        if (pks.isEmpty()) {
            rs = dbmd.getColumns(catalog, schema, table, null);
            while (rs.next()) {
                ColumnDefinition colPK = new ColumnDefinition();
                colPK.setColName(rs.getString("column_name"));
                ResultSet rsColumn = dbmd.getColumns(catalog, schema, table, rs.getString("column_name"));
                while (rsColumn.next()) {
                    colPK.setType(rsColumn.getString("type_name"));
                }
                pks.add(colPK);
            }
        }
        return pks;
    }

    /**
     * executes SQL code on database
     * @param sql SQL code to be executed
     */
    public void execute(String sql) throws SQLException {
        con.createStatement().execute(sql);
    }
}
